package com.east.spark.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class CreateGbaseTableFromExcel2 {

	private static String mainSheet = "数据采集接入";
	private static final String lineSeparator = "\n";
	private static final String clumnSeparator = "\t";

	public static void main(String[] args) throws Exception {

		// String excelPath =
		// "D:/newworkspace/my-study-java/doc/浙江移动数据共享平台模型规范_综资系统数据-GBase(新)(1).xlsx";
		// String excelPath =
		// "D:/newworkspace/my-study-java/doc/浙江移动数据共享平台模型规范_综资系统数据-GBase(liuxiangke).xlsx";
		String excelPath = "D:/newworkspace/my-study-java/doc/浙江移动数据共享平台模型规范_综资系统数据-GBase.xlsx";

		InputStream inputStream = new FileInputStream(excelPath);
		Workbook wb = WorkbookFactory.create(inputStream);
		Sheet sheet = wb.getSheet(mainSheet);

		List<String> sheets = new ArrayList<>();

		int lastRow = sheet.getLastRowNum();
		for (int i = 4; i <= lastRow + 1; i++) {
			Row row = sheet.getRow(i - 1);
			String cellValue = row.getCell(4 - 1).getStringCellValue();
			sheets.add(StringUtils.trim(cellValue));
		}

		StringBuffer sqls = new StringBuffer();

		for (String singleSheet : sheets) {

			GbaseTableInfo gbaseTableInfo = loadColumnDesc(wb, singleSheet);

			String sql = createSql(gbaseTableInfo, singleSheet);
			sqls.append(sql).append(lineSeparator);
		}
		System.out.println(sqls.toString());
		String filePath = "D:/newworkspace/my-study-java/doc/zongZiSql.sql";
		writeStringToFile(filePath, sqls.toString());
	}

	public static void writeStringToFile(String filePath, String sqls) {
		try {
			FileOutputStream fos = new FileOutputStream(filePath);
			fos.write(sqls.getBytes());
			fos.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void writeSingleStringToFile(String filePath, String sql) {
		try {

			FileOutputStream fos = new FileOutputStream(filePath);
			fos.write(sql.getBytes());
			fos.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unused")
	private static String createSql(GbaseTableInfo gbaseTableInfo, String sheetName) {
		StringBuffer stringBuffer = new StringBuffer();

		stringBuffer = stringBuffer.append(sheetName).append(":").append("select ");

		List<GbaseColumnInfo> gbaseColumnInfos = gbaseTableInfo.getColumns();

		List<String> columns = new ArrayList<>();
		for (GbaseColumnInfo gbaseColumnInfo : gbaseColumnInfos) {
			String name = gbaseColumnInfo.getName();
			columns.add(name);
		}
		String columsString = StringUtils.join(columns, ",");

		stringBuffer.append(columsString).append(" from irm.").append(sheetName);

		return stringBuffer.toString().toLowerCase();

	}

	private static GbaseTableInfo loadColumnDesc(Workbook wb, String sheetName) throws Exception {

		GbaseTableInfo gbaseTableInfo = new GbaseTableInfo();

		Sheet sheet = wb.getSheet(sheetName);
		if (sheet == null) {
			throw new RuntimeException("Sheet Not Found: " + sheetName);
		}

		List<GbaseColumnInfo> gbaseColumnInfos = new ArrayList<>();

		int lastRow = sheet.getLastRowNum();
		for (int i = 4; i <= lastRow + 1; i++) {
			Row row = sheet.getRow(i - 1);

			GbaseColumnInfo columnInfo = new GbaseColumnInfo();

			columnInfo.setName(row.getCell(1 - 1).getStringCellValue());
			columnInfo.setDesc(row.getCell(2 - 1).getStringCellValue());
			columnInfo.setType(row.getCell(3 - 1).getStringCellValue());

			gbaseColumnInfos.add(columnInfo);
		}

		gbaseTableInfo.setColumns(gbaseColumnInfos);
		gbaseTableInfo.setTableDesc(sheet.getRow(2 - 1).getCell(1 - 1).getStringCellValue());
		gbaseTableInfo.setTableName(sheet.getRow(2 - 1).getCell(2 - 1).getStringCellValue());

		return gbaseTableInfo;
	}

}
